﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using WL.Model;
using WL.Dal;
using WL.Bll;

namespace WL
{
    public partial class Form1 : Form
    {
        
        public Form1()
        {
            InitializeComponent();
        }

      

        private List<Product> list = new List<Product>();
        private string tmp = "";
        private string upfile = "";
        private int index=-1; //修改行
        private Table t = null;

        private void button1_Click(object sender, EventArgs e)
        {
            int id = Convert.ToInt32(((CmbItem)(cmbTables.SelectedItem)).Id);
            Table t = TableDal.getTableById(id);

            FileDialog f = new OpenFileDialog();
            f.ShowDialog();
            //tbSSFExcel.Text = f.FileName;
            if (f.FileName.Trim().Equals("")) return;

            //List<string[]> l = ExcelMM.getDatas(f.FileName,"QAS");
            //dgvFields.DataSource = l;
            DataTable dt = ExcelMM.getData(f.FileName, "QAS");
            dgvErrors.DataSource = dt;

            
        }

        private void button3_Click(object sender, EventArgs e)
        {
            
            initCmb();
            
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //load tables
            initCmb();

           
            initCmbFileName();
            initTables();
            modifyStatus();

        }
        private void initCmbFileName()
        {
            string[] fn = Bll.ProductService.getFileNams();
            //cmbFileName.Items.Clear();
            //cmbFileName.Items.Add(new CmbItem("0","全部"));
            //foreach (string name in fn)
            //{
            //    CmbItem ci = new CmbItem(name, name);
            //    cmbFileName.Items.Add(ci);
            //}
        }
        private void initCmb()
        {
            cmbTables.Items.Clear();
            DataTable dt = TableDal.getAllTable();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                CmbItem ci = new CmbItem(dt.Rows[i]["id"].ToString(), dt.Rows[i]["name"].ToString());
                cmbTables.Items.Add(ci);
                cmbTables2.Items.Add(ci);
            }
        }
        

        private void button4_Click(object sender, EventArgs e)
        {
            if (cmbTables.SelectedIndex == -1)
            {
                MessageBox.Show("选择上传表格");
                return;
            }

            string id = (((CmbItem)cmbTables.SelectedItem).Id);
            
            t = Bll.TableService.getFields(id);

            FileDialog f = new OpenFileDialog();
            f.ShowDialog();
            if (f.FileName.Trim().Equals("")) return;

            if (tbSheet.Text.Equals(""))
            {
                MessageBox.Show("请先填写工作表名");
                return;
            }
            DataTable dt = ExcelMM.getDataTableByExcel(f.FileName, tbSheet.Text);//excel to datatable

            DataService.SaveByDataTable(dt, t);
            MessageBox.Show("导入完成");
        }

        private void cmbTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            int id = Convert.ToInt32(((CmbItem)cmbTables.SelectedItem).Id);
            Table t = TableDal.getTableById(id);
            tbIgnore.Text = t.ignore.ToString();
            tbSheet.Text = t.sheetname;
            dgvErrors.DataSource = t.fields;

            int max = 0;//这个max是用来确定当前表格sortid的，每个表格不能重复，所以求出最大值，插入的时候就插入比最大值+1的
            for (int i = 0; i < t.fields.Count; i++)
            {
                max = max < t.fields[i].sortid ? t.fields[i].sortid : max;
            }
            tbMax.Text = max +1+ "";
        }

       

        private void button5_Click(object sender, EventArgs e)
        {
           Table tc = new Table();
            
            Type t = tc.GetType();//获得该类的Type  
            //再用Type.GetProperties获得PropertyInfo[],然后就可以用foreach 遍历了  
            foreach (PropertyInfo pi in t.GetProperties()  )
            {  
                object value1 = pi.GetValue(tc, null);//用pi.GetValue获得值  
                string name = pi.Name;//获得属性的名字,后面就可以根据名字判断来进行些自己想要的操作  
                //获得属性的类型,进行判断然后进行以后的操作,例如判断获得的属性是整数  
               if(value1.GetType() == typeof(int))  
               {  
                   //进行你想要的操作  
               }  
            }  
        }


        /// <summary>
        /// 点击导入excel数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button6_Click(object sender, EventArgs e)
        {
            if (cmbTables.SelectedIndex == -1)
            {
                MessageBox.Show("选择上传表格");
                return;
            }

            string id = (((CmbItem)cmbTables.SelectedItem).Id);
            t = Bll.TableService.getFields(id);
            
            FileDialog f = new OpenFileDialog();
            f.ShowDialog();
            if (f.FileName.Trim().Equals("")) return;
            //DataTable dt = ExcelMM.getData(f.FileName, "QAS");//excel to datatable
            DataTable dt = ExcelMM.getDataTableByExcel(f.FileName, "Sheet1");//excel to datatable

            upfile = getfilename(f.FileName);//获取文件名

            string[] fn = Bll.ProductService.getFileNams();

            foreach(string filename in fn) {
                if (filename.Equals(upfile))
                {
                    DialogResult dr = MessageBox.Show("发现数据库内已经存在与本表同名上传的数据\n是否要删除后再上传？", "提示信息", MessageBoxButtons.YesNoCancel);
                    
                    if (dr == DialogResult.Cancel)
                    {
                        return;
                    }
                    else if (dr==DialogResult.Yes)
                    {
                        Bll.ProductService.deleteProductByUpfile(upfile);
                    }
                }
            }

            add(dt, upfile);
            initCmbFileName();
            dgvErrors.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            MessageBox.Show("导入完成");
            
           // Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;


            //dgvFields.DataSource = dt;
        }

        //从带路径的文件名中获取文件名
        private string getfilename(string filename)
        {
            string[] qq = filename.Split('\\');
            if (qq.Length > 0)
                return qq[qq.Length - 1];
            else return "";
        }

        private void button7_Click(object sender, EventArgs e)
        {
            //string upfile = null;
            //if (cmbFileName.SelectedIndex > 0)
            //{

            //    upfile = ((CmbItem)(cmbFileName.SelectedItem)).Id;
            //    //MessageBox.Show(upfile);
            //}

            //dgvDatas.DataSource = Bll.ProductService.getDatas(tbNo.Text, upfile);
            //lblRows.Text = "总数：" + dgvDatas.Rows.Count;
            ////autoSize(dgvDatas);

            //datasHeadText(dgvDatas);

        }
        private void datasHeadText(DataGridView dgv)
        {
            dgv.Columns[1].HeaderText = "型号";
            dgv.Columns[2].HeaderText = "库存";
            dgv.Columns[3].HeaderText = "包装数量";
            dgv.Columns[4].HeaderText = "供应商";
            dgv.Columns[5].HeaderText = "价格";
            dgv.Columns[6].HeaderText = "备注";
            dgv.Columns[7].HeaderText = "上传文件";
            dgv.Columns[8].HeaderText = "行数";
        }

        /// <summary>
        /// 自动调整列宽
        /// </summary>
        /// <param name="dgv"></param>
        private void autoSize(DataGridView dgv)
        {
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                dgv.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string id = (((CmbItem)cmbTables.SelectedItem).Id);
            t = Bll.TableService.getFields(id);

            for (int i = 0; i < dgvErrors.Rows.Count; i++)
            {
                DataGridViewRow row = dgvErrors.Rows[i];

                DataTable dt = (DataTable)dgvErrors.DataSource;

                add(dt, upfile);
               
                //atteDal.SaveOrUpdate(atte);
            }
            
            initCmbFileName();
            MessageBox.Show("导入完成");
        }

        private void add(DataTable dt, string upfile)
        {
            int count = dt.Rows.Count;
            //dt = Bll.ProductService.add(dt, upfile, t);
            Bll.ProductService.addByFields(dt, t); //真正插入数据
            dgvErrors.DataSource = dt;
            lblError.Text = "错误:" + dt.Rows.Count;
            lblSuccess.Text = "成功:" + (count - dt.Rows.Count);
            
            //显示/隐藏导入错误数据按钮
            if (dt.Rows.Count > 0)
            {
                btnErrorImport.Visible = true;
            }
            else
            {
                btnErrorImport.Visible = false;
            }
            //MessageBox.Show("导入完成");
        }

        private void button8_Click(object sender, EventArgs e)
        {
            //if (dgvDatas.SelectedRows.Count < 1)
            //{
            //    MessageBox.Show("请选择需要删除项所在行的单元格");
            //    return;
            //}
           
             DialogResult dr = MessageBox.Show("确定将删除记录？", "提示信息", MessageBoxButtons.YesNo);
             if (dr == DialogResult.No)
             {
                 return;
             }

            //for (int i = 0; i < dgvDatas.SelectedRows.Count; i++)
            //{
            //    string id = dgvDatas.SelectedRows[i].Cells[0].Value.ToString();
            //    Bll.ProductService.deleteProductById(id);
            //}

            //dgvDatas.DataSource = Bll.ProductService.getDatas(tbNo.Text, null);
        }
        /// <summary>
        /// 更改状态
        /// </summary>
        private void modifyStatus()
        {
            if (index == -1)
            {
                //lblStatus.Text = "查看状态";
                lblState.Text = "查看状态";
            }
            else
            {
                lblState.Text = "编辑状态";
                //lblStatus.Text = "编辑状态";
            }
        }
        private void button9_Click(object sender, EventArgs e)
        {
            if (index == -1)
            {

                //index = dgvDatas.SelectedRows[0].Index;
                //dgvDatas.Rows[index].DefaultCellStyle.BackColor = Color.Red;
            }
            else
            {
                //dgvDatas.Rows[index].DefaultCellStyle.BackColor = Color.White;
                foreach (Product p in list)
                {
                    Bll.ProductService.update(p);
                }
                list.Clear();
                initCmbFileName();
                MessageBox.Show("修改成功");
                index = -1;
            }
            //.Rows[i].DefaultCellStyle.BackColor = Color.Red;


            modifyStatus();
        }

        private void dgvDatas_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            //string s = dgvDatas.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
            //tmp = s;
            
            //dgvDatas.Rows[e.RowIndex].Cells[e.ColumnIndex] = System.Windows.Forms.DataGridViewEditMode.EditOnEnter;
            //this.dgvDatas.EditMode = System.Windows.Forms.DataGridViewEditMode.EditOnEnter;
        }

        private void dgvDatas_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //string s = dgvDatas.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
            //if (!s.Equals(tmp))
            //{
            //    if (e.RowIndex == index)
            //    {
            //        addToModifyList(dgvDatas.Rows[e.RowIndex]);
            //    }
            //}
            //tmp = "";
        }


        private void addToModifyList(DataGridViewRow row)
        {
            try
            {
                Product p = new Product();
                p.id = Convert.ToInt32(row.Cells[0].Value);
                p.no = row.Cells[1].Value.ToString();
                p.freeStock = Convert.ToInt32(row.Cells[2].Value);
                p.packQty = Convert.ToInt32(row.Cells[3].Value);
                p.manufacturer = row.Cells[4].Value.ToString();
                p.price = Convert.ToDecimal(row.Cells[5].Value.ToString());
                p.remark = row.Cells[6].Value.ToString();
                p.upfile = row.Cells[7].Value.ToString();
                p.line = Convert.ToInt32( row.Cells[8].Value.ToString());
                list.Add(p);
            }
            catch (Exception e)
            {
                
            }
        }

        private void dgvDatas_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            MessageBox.Show("修改的数据不符合规则");
        }

        
        /// 单击单元格时触发
        private void dgvDatas_CellClick(object sender, DataGridViewCellEventArgs e)
        {
           //// MessageBox.Show(index.ToString());
           // MessageBox.Show(e.RowIndex.ToString());
            if (e.RowIndex == index)
            {

                //dgvDatas.ReadOnly = false;
               // dgvDatas.EditMode = DataGridViewEditMode.EditOnEnter;
            }
            else
            {
               // dgvDatas.ReadOnly = true;
            }
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            if (listBox1.SelectedItems.Count > 0)
            {
                string id = (((CmbItem)listBox1.SelectedItem).Id);
                if (Bll.TableService.delTable(id))
                {
                    MessageBox.Show("已经删除");
                    initTables();
                }
                else
                {
                    MessageBox.Show("删除错误");
                }
            }

            ////创建表
            //FrmTableAdd f = new FrmTableAdd();
            //f.ShowDialog();
            //initCmb();


           // dataGridView1.DataSource = TableMM.getAllTable();
        }
        /// <summary>
        /// 初始化表格列表
        /// </summary>
        private void initTables()
        {
            listBox1.Items.Clear();
            foreach (DataRow row in TableDal.getAllTable().Rows)
            {
                CmbItem cb = new CmbItem(row["id"].ToString(), row["name"].ToString());
                listBox1.Items.Add(cb);
            }
        }

        private void button10_Click(object sender, EventArgs e)
        {
            FrmTableAdd f = new FrmTableAdd();
            f.ShowDialog();
            initTables();
            //DataTable dt = new DataTable();
            //dt.Columns.Add("id");
            //dt.Columns.Add("name");
            //dt.Columns.Add("type");
            //dt.Columns.Add("typeid");
            //dt.Columns.Add("table");
            //dt.Columns.Add("sortid");

            //DataRow dr= dt.NewRow();
            //dr[0] = 1;

            //dgvFields.Rows.Add(dr);
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox1.SelectedItems.Count > 0)
            {
                initFields();
            }
        }
        /// <summary>
        /// 点击表名初始化其列的值
        /// </summary>
        private void initFields()
        {
            DataTable dt = new DataTable();

            DataColumn dtc = new DataColumn("id", typeof(Int32));
            dt.Columns.Add(dtc);
            dtc = new DataColumn("列名", typeof(String));
            dt.Columns.Add(dtc);
            dtc = new DataColumn("类型", typeof(String));
            dt.Columns.Add(dtc);

            foreach (Field f in Bll.TableService.getFields(((CmbItem)listBox1.SelectedItem).Id).fields)
            {
                DataRow r =  dt.NewRow();
                r[0] = f.id;
                r[1] = f.name;
                r[2] = f.type;
                dt.Rows.Add(r);
            }

            dgvField.DataSource = dt;
            dgvField.Columns[0].Visible = false;
        }

        private void button12_Click(object sender, EventArgs e)
        {
            FrmFieldAdd f = new FrmFieldAdd()
            ;
            f.ShowDialog();
        }

        private void button11_Click(object sender, EventArgs e)
        {
            int count = dgvField.SelectedRows.Count ;
             
            if (count > 0)
            {
                for (int i = 0; i < count; i++)
                {
                    Bll.TableService.delField(dgvField.SelectedRows[i].Cells[0].Value.ToString());
                }
                MessageBox.Show("删除完成");
                listBox1_SelectedIndexChanged(null, null);
            }
        }

        private void button13_Click(object sender, EventArgs e)
        {
            if (dgvField.SelectedRows.Count > 0)
            {
                try
                {
                    int id = Convert.ToInt32(dgvField.SelectedRows[0].Cells[0].Value);
                    FrmFieldAdd f = new FrmFieldAdd(id);
                    f.ShowDialog();
                    initFields();
                }
                catch (Exception ex) { }
            }
        }

        private void button14_Click(object sender, EventArgs e)
        {
            if (listBox1.SelectedItems.Count > 0)
            {
                int id = Convert.ToInt32(((CmbItem)listBox1.SelectedItem).Id);
                FrmTableAdd f = new FrmTableAdd(id);
                f.ShowDialog();
                initTables();
            }
        }

        private void button16_Click(object sender, EventArgs e)
        {

            int count = dgvField.SelectedRows.Count;

            if (count > 0)
            {
                for (int i = 0; i < count; i++)
                {
                    string tbname = ((CmbItem)listBox1.SelectedItem).Name;//表名
                    string column = dgvField.SelectedRows[i].Cells[1].Value.ToString();//被删的列
                    Bll.TableService.delField(dgvField.SelectedRows[i].Cells[0].Value.ToString());
                    Bll.TableService.delColunm(column, tbname);
                }
                MessageBox.Show("删除完成");
                listBox1_SelectedIndexChanged(null, null);
            }
        }

        private void button18_Click(object sender, EventArgs e)
        {
            if (cmbTables2.SelectedItem == null)
            {
                MessageBox.Show("请选择查询数据的表");
                return;
            }
            string id = (((CmbItem)cmbTables2.SelectedItem).Id);
            Table t = Bll.TableService.getFields(id);

            dgvData.DataSource = Bll.DataService.getData(t, tbKey.Text);
        }

        private void button15_Click(object sender, EventArgs e)
        {
            if (index == -1)
            {

                index = dgvData.SelectedRows[0].Index;
                dgvData.Rows[index].DefaultCellStyle.BackColor = Color.Red;
            }

        }

        private void button17_Click(object sender, EventArgs e)
        {
            if (dgvData.SelectedRows.Count < 1)
            {
                MessageBox.Show("请选择需要删除项所在行的单元格");
                return;
            }

            DialogResult dr = MessageBox.Show("确定将删除记录？", "提示信息", MessageBoxButtons.YesNo);
            if (dr == DialogResult.No)
            {
                return;
            }
            string id = (((CmbItem)cmbTables2.SelectedItem).Id);
            Table t = Bll.TableService.getFields(id);

            for (int i = 0; i < dgvData.SelectedRows.Count; i++)
            {
                id = dgvData.SelectedRows[i].Cells[0].Value.ToString();
                //MessageBox.Show(id);
                Bll.DataService.delById(t, id);
            }
            MessageBox.Show("操作完成");
            button18_Click(null, null);
           // dgvData.DataSource = Bll.ProductService.getDatas(tbNo.Text, null);
        }

       

       

    

        private void button19_Click(object sender, EventArgs e)
        {
            if (index != -1) //防止没进入编辑状态就保存
            {
                //取出修改的字段he值
                string values = "";
                for (int i = 1; i < dgvData.ColumnCount; i++)
                {
                    values += string.Format("{0}='{1}',", dgvData.Columns[i].HeaderText, dgvData.Rows[index].Cells[i].Value);
                }

                values = values.Substring(0, values.Length - 1);

                string id = (((CmbItem)cmbTables2.SelectedItem).Id);
                Table t = Bll.TableService.getFields(id);

                int dataid = Convert.ToInt32(dgvData.Rows[index].Cells[0].Value); //
                
                //修改
                if (Bll.DataService.update(t, dataid, values))
                {
                    MessageBox.Show("更新成功");
                    lblState.Text = "查看状态";
                    dgvData.Rows[index].DefaultCellStyle.BackColor = Color.White;
                    index = -1;
                }
                else
                {
                    MessageBox.Show("更新失败");
                }
            }
        }

        private void dgvData_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex == index)
            {
                dgvData.ReadOnly = false;
                dgvData.EditMode = DataGridViewEditMode.EditOnEnter;
            }
            else
            {
                dgvData.ReadOnly = true;
            }
        }

        private void dgvData_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            MessageBox.Show("数据类型不正确");
        }

      

       
    }
}
